package com.adams.custom.page.core.dao.impl;

import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.StringTokenizer;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import org.apache.log4j.Logger;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;

import com.adams.access.db.bean.DicTable;
import com.adams.access.db.dao.hibernate.HibernateGenericDao;
import com.adams.core.exception.BusinessException;
import com.adams.custom.page.util.ViewCacheUtil;
import com.adams.custom.page.util.ViewUtil;
import com.adams.encache.mechanism.CacheUtil;
import com.adams.utils.SpringBeanFactoryUtil;

public class DynamicDBDaoImpl extends HibernateGenericDao<DicTable, String>
{

	Logger logger = Logger.getLogger(DynamicDBDaoImpl.class.getName());

	// 查询二级缓存key
	// 保存sql语句得到返回数据库查询结构集的Map key为查询的sql语句 value为查询结果集
	private static String cacheResultSetBySql = CacheUtil.VIEW_DATA_CACHE_NAME;

	/**
	 * Description : 根据表名得到数据库连接对象
	 * 
	 * @param tablecode
	 * @return
	 * @throws BusinessException
	 * 
	 */
	private SessionFactory getConnectionBean(String beanname)
			throws BusinessException
	{
		Object result = SpringBeanFactoryUtil.getBean(beanname);
		if (!(result instanceof SessionFactory))
		{
			logger.error("服务配置错误，没对应" + beanname + "不是SessionFactory对象!");
			throw new BusinessException("database.connectionbean.exception");
		}
		return (SessionFactory) result;
	}

	/**
	 * Description : 执行记录总数查询语句
	 * 
	 * @param selectCountSql
	 * @return
	 * 
	 */
	public int getCount(String connectionBeanName, String selectCountSql)
			throws BusinessException
	{
		Object count = null;
		Cache cache = CacheUtil.getCache(cacheResultSetBySql);
		Element element = cache.get(selectCountSql);
		if (null != element)
		{
			count = (Object) element.getObjectValue();
		} else
		{
			SessionFactory sessionFactory = getConnectionBean(connectionBeanName);
			Session session = sessionFactory.getCurrentSession();
			logger.info("执行的sql语句:" + selectCountSql);
			SQLQuery sQLQuery = session.createSQLQuery(selectCountSql);
			count = sQLQuery.uniqueResult();
			element = new Element(selectCountSql, count);
			cache.put(element);
			addCacheBySql(selectCountSql);
		}

		if (null == count)
		{
			return 0;
		} else
		{
			String countStr = count.toString();
			if (!"".equals(countStr) && countStr.matches("\\d+"))
			{
				return Integer.valueOf(countStr).intValue();
			} else
			{
				return 0;
			}

		}
	}

	/**
	 * Description : 执行动态sql分页查询
	 * 
	 * @param selectSql
	 * @param start
	 * @param limit
	 * @return
	 * 
	 */
	@SuppressWarnings("unchecked")
	public List findData(String connectionBeanName, String selectSql,
			int start, int limit) throws BusinessException
	{
		List result = null;

		String sql = selectSql;
		if (limit > 0)
		{
			if (start > 0)
			{
				sql = selectSql + " limit " + start + ", " + limit;
			} else
			{
				sql = selectSql + " limit " + limit;
			}
		}

		Cache cache = CacheUtil.getCache(cacheResultSetBySql);
		Element element = cache.get(sql);
		if (null != element)
		{
			result = (List) element.getObjectValue();
		} else
		{
			SessionFactory sessionFactory = getConnectionBean(connectionBeanName);
			Session session = sessionFactory.getCurrentSession();

			logger.info("执行的sql语句:" + sql);
			SQLQuery sQLQuery = session.createSQLQuery(sql);
			result = sQLQuery.list();
			element = new Element(sql, result);
			cache.put(element);
			addCacheBySql(sql);
		}

		return result;
	}

	/**
	 * Description :执行sql
	 * 
	 * @param sql
	 * @return
	 * 
	 */
	public int execute(String connectionBeanName, String sql)
			throws BusinessException
	{
		SessionFactory sessionFactory = getConnectionBean(connectionBeanName);
		Session session = sessionFactory.getCurrentSession();
		logger.info("执行的sql语句:" + sql);
		SQLQuery sQLQuery = session.createSQLQuery(sql);
		Integer result = Integer.valueOf(sQLQuery.executeUpdate());
		clearCacheBySql(sql);
		return result;
	}

	/**
	 * Description : 增加数据
	 * 
	 * @param connectionBeanName
	 * @param sql
	 * @return
	 * @throws BusinessException
	 * 
	 */
	public int insert(String connectionBeanName, String sql)
			throws BusinessException
	{
		SessionFactory sessionFactory = getConnectionBean(connectionBeanName);
		Session session = sessionFactory.getCurrentSession();
		logger.info("执行的sql语句:" + sql);
		SQLQuery sQLQuery = session.createSQLQuery(sql);
		Integer.valueOf(sQLQuery.executeUpdate());
		SQLQuery identitySql = session
				.createSQLQuery("select last_insert_id()");
		Object identity = identitySql.uniqueResult();
		int result = 0;
		if (null != identity)
		{
			String identityStr = identity.toString();
			result = Integer.valueOf(identityStr).intValue();
		}
		clearCacheBySql(sql);
		return result;
	}

	/**
	 * Description : 根据sql清空查询数据缓存(更新修改删除方法中使用)
	 * 
	 * @param sql
	 * 
	 */
	private static void clearCacheBySql(String sql)
	{
		Set<String> tablecodes = getTablecodeBySql(sql);
		for (String tablecode : tablecodes)
		{
			CacheUtil.clearCacheByTablecode(tablecode);
		}
	}

	/**
	 * Description : 根据sql 产生表与sql语句的关系缓存Map
	 * 
	 * @param sql
	 * 
	 */
	@SuppressWarnings("unchecked")
	private static void addCacheBySql(String sql)
	{
		Map tableMap = ViewCacheUtil
				.getServiceCacheMap(CacheUtil.cacheSqlsByTablecode);
		Set<String> tablecodes = getTablecodeBySql(sql);
		if (null != tablecodes && tablecodes.size() > 0)
		{
			Set<String> sqls = null;
			for (String tablecode : tablecodes)
			{
				if (tableMap.containsKey(tablecode))
				{
					sqls = (Set) tableMap.get(tablecode);
				} else
				{
					sqls = new HashSet<String>();
				}
				sqls.add(sql);
				tableMap.put(tablecode, sqls);
			}
		}
	}

	/**
	 * Description : 从sql语句中提取所有表名
	 * 
	 * @param sql
	 * @return
	 * 
	 */
	private static Set<String> getTablecodeBySql(String srcSql)
	{
		String insertflag = "INSERT INTO ";
		String updateflag = "UPDATE ";
		String deleteflag = "DELETE ";
		String fromflag = " FROM ";
		String whereflag = " WHERE ";
		Set<String> result = new HashSet<String>();
		String tablecode = "";
		if (ViewUtil.isNuLLStr(srcSql))
		{
			return result;
		}
		String upperSql = srcSql.trim().toUpperCase();
		if (upperSql.indexOf(insertflag.toUpperCase()) == 0)
		{
			// 插入 sql 语句
			int endIndex = upperSql.indexOf(" ", insertflag.length());
			if (endIndex != -1)
			{
				tablecode = upperSql.substring(insertflag.length(), endIndex);
				result.add(tablecode);
				return result;
			}
			endIndex = upperSql.indexOf("(", insertflag.length());
			if (endIndex != -1)
			{
				tablecode = upperSql.substring(insertflag.length(), endIndex);
				result.add(tablecode);
				return result;
			}
			return result;
		} else if (upperSql.indexOf(updateflag) == 0)
		{
			// 更新 sql 语句
			int endIndex = upperSql.indexOf(" ", updateflag.length());
			if (endIndex != -1)
			{
				tablecode = upperSql.substring(updateflag.length(), endIndex);
				result.add(tablecode);
				return result;
			}
			return result;
		} else if (upperSql.indexOf(deleteflag) == 0)
		{
			// 删除 sql 语句
			int beginIndex = upperSql.indexOf(fromflag);
			if (beginIndex > 0)
			{
				int endIndex = upperSql.indexOf(" ", beginIndex
						+ fromflag.length());
				if (endIndex != -1)
				{
					tablecode = upperSql.substring(beginIndex
							+ fromflag.length(), endIndex);
					result.add(tablecode);
					return result;
				}
				tablecode = upperSql.substring(beginIndex + fromflag.length());
				result.add(tablecode);
				return result;
			}
			return result;
		} else
		{
			// 查询 sql 语句
			int beginIndex = 0;
			beginIndex = upperSql.indexOf(fromflag, beginIndex);
			while (beginIndex != -1)
			{
				beginIndex = beginIndex + fromflag.length();
				int endIndex = -1;
				int wherepos = upperSql.indexOf(whereflag, beginIndex);
				int leftBpos = upperSql.indexOf("(", beginIndex);
				int rightBpos = upperSql.indexOf(")", beginIndex);
				if (wherepos != -1)
				{
					endIndex = wherepos;
				}
				if ((endIndex == -1 && leftBpos != -1)
						|| (endIndex > leftBpos && leftBpos != -1))
				{
					endIndex = leftBpos;
				}
				if ((endIndex == -1 && rightBpos != -1)
						|| (endIndex > rightBpos && rightBpos != -1))
				{
					endIndex = rightBpos;
				}
				if (endIndex != -1)
				{
					tablecode = upperSql.substring(beginIndex, endIndex);
					Set<String> tablecodes = getMultiTablecodeByFrom(tablecode);
					result.addAll(tablecodes);
					beginIndex = upperSql.indexOf(fromflag, beginIndex);
					continue;
				} else
				{
					tablecode = upperSql.substring(beginIndex);
					Set<String> tablecodes = getMultiTablecodeByFrom(tablecode);
					result.addAll(tablecodes);
					break;
				}
			}
		}
		return result;
	}

	private static Set<String> getMultiTablecodeByFrom(String fromtable)
	{
		Set<String> result = new HashSet<String>();
		StringTokenizer st = new StringTokenizer(fromtable, ",");
		while (st.hasMoreTokens())
		{
			String table = st.nextToken();
			if (ViewUtil.isNuLLStr(table))
			{
				continue;
			}
			int pos = table.indexOf(" ");
			if (pos != -1)
			{
				table = table.substring(0, pos);
			}
			result.add(table);
		}
		return result;
	}

	public static void main(String[] args)
	{
	}
}
